﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Reflection;
using System.Text;
using PoliAsis.Domain.Common;

namespace PoliAsis.Domain.Abstract
{
    public abstract class DataContex<T> : ControlObjectState<T> where T : class
    {
        private SqlTransaction db_transaction;
        QueryMap querymap = QueryMap.Map;
        QueryGenerator<T> querygen;
        Validator _validator;

        public DataContex()
        {
            querygen = new QueryGenerator<T>();
            _validator = new Validator();
        }

        public virtual T SelectOne(T where_object)
        {
            return Execute(where_object, null, EXECUTE_MODE.SELECT_PAGING).FirstOrDefault();
        }
        public virtual List<T> SelectMany(T where_object)
        {
            return Execute(where_object, null, EXECUTE_MODE.SELECT_MANY);
        }
        public virtual List<T> SelectAll()
        {
            var o = Activator.CreateInstance<T>();
            return Execute(o, null, EXECUTE_MODE.SELECT);
        }
        public virtual List<T> SearchBegins(T where_object, int pageNumber, int pageSize, out int numRecords)
        {
            int start_row = (pageNumber * pageSize) + 1;
            int end_row = (start_row + pageSize) - 1;
            return Execute(where_object, null, EXECUTE_MODE.SEARCH_PAGING, out numRecords, start_row, end_row, SEARCH_MODE.SEARCH_BEGINS_WITH);
        }
        public virtual List<T> Search(T where_object, int pageNumber, int pageSize, out int numRecords)
        {
            int start_row = (pageNumber * pageSize) + 1;
            int end_row = (start_row + pageSize) - 1;
            return Execute(where_object, null, EXECUTE_MODE.SEARCH_PAGING, out numRecords, start_row, end_row, SEARCH_MODE.SEARCH_CONTAINS);
        }
        public virtual List<T> SelectPaging(T where_object, int pageNumber, int pageSize, out int numRecords)
        {
            int start_row = (pageNumber * pageSize) + 1;
            int end_row = (start_row + pageSize) - 1;
            return Execute(where_object, null, EXECUTE_MODE.SELECT_PAGING, out numRecords, start_row, end_row);
        }

        public virtual int Insert(T insert_object)
        {
            if (insert_object == null)
                return 0;

            Execute(insert_object, null, EXECUTE_MODE.SINGLE_INSERT);

            return 1;
        }
        public virtual int Insert(List<T> list_insert_obj)
        {
            if (list_insert_obj == null || (list_insert_obj != null && list_insert_obj.Count == 0))
                return 0;

            Execute(null, list_insert_obj, EXECUTE_MODE.DATA_TABLE_INSERT);

            return 1;

        }
        public virtual int UpdateOne(T update_object)
        {
            if (update_object == null)
                return 0;

            Execute(update_object, null, EXECUTE_MODE.SINGLE_UPDATE);

            return 1;
        }
        public virtual int DeleteOne(T delete_object)
        {
            if (delete_object == null)
                return 0;

            Execute(delete_object, null, EXECUTE_MODE.SINGLE_DELETE);

            return 1;
        }
        public virtual int DeleteMany(T delete_object)
        {
            if (delete_object == null)
                return 0;

            Execute(delete_object, null, EXECUTE_MODE.MULTIPLE_DELETE);

            return 1;
        }

        private List<T> Execute(T exec_object, List<T> list_exec_objs, EXECUTE_MODE mode, int start_row = 1, int end_row = 10, SEARCH_MODE search_mode = SEARCH_MODE.SEARCH_CONTAINS)
        {
            int num_records = 0;
            return Execute(exec_object, list_exec_objs, mode, out num_records, start_row, end_row, search_mode);
        }
        private List<T> Execute(T exec_object, List<T> list_exec_objs, EXECUTE_MODE mode, out int numRecords, int start_row = 1, int end_row = 10, SEARCH_MODE search_mode = SEARCH_MODE.SEARCH_CONTAINS)
        {
            numRecords = -1;
            int number_of_records = -1;
            List<T> items = null;
            StringBuilder query = new StringBuilder();
            SqlConnection conn = GetConnection();
            SqlCommand cmd = conn.CreateCommand();
            if (db_transaction != null)
            {
                cmd.Transaction = db_transaction;
            }

            cmd.CommandType = CommandType.Text;
            QueryDesc q = new QueryDesc();
            switch (mode)
            {
                case EXECUTE_MODE.SINGLE_INSERT:
                    OnBeforeSave(exec_object);
                    q = querygen.InsertQuery(exec_object);
                    break;

                case EXECUTE_MODE.DATA_TABLE_INSERT:
                    OnBeforeSave(list_exec_objs);
                    q = querygen.InsertDtQuery(list_exec_objs);
                    break;

                case EXECUTE_MODE.SINGLE_UPDATE:
                    OnBeforeUpdate(exec_object);
                    q = querygen.UpdateQuery(exec_object);
                    break;

                case EXECUTE_MODE.SINGLE_DELETE:
                    OnBeforeDelete(exec_object);
                    q = querygen.DeleteQuery(exec_object);
                    break;

                case EXECUTE_MODE.MULTIPLE_DELETE:
                    OnBeforeDelete(exec_object);
                    q = querygen.DeleteQuery(exec_object);
                    break;

                case EXECUTE_MODE.SELECT:
                    items = new List<T>();
                    q = querygen.SelectQuery(exec_object);
                    break;

                case EXECUTE_MODE.SELECT_MANY:
                    items = new List<T>();
                    q = querygen.SelectWhereQuery(exec_object);
                    break;

                case EXECUTE_MODE.SELECT_PAGING:
                    items = new List<T>();
                    q = querygen.SelectQueryPaging(exec_object, start_row, end_row);
                    break;

                case EXECUTE_MODE.SEARCH_PAGING:
                    items = new List<T>();
                    q = querygen.SearchQueryPaging(exec_object, start_row, end_row, search_mode);
                    break;

                default: return items;

            }

            cmd.Parameters.AddRange(q.parameters);
            cmd.CommandText = q.query;

            try
            {
                if (db_transaction == null)
                    conn.Open();


                if (items != null)
                {
                    //read items
                    bool has_total_row_column = true;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {

                            if (has_total_row_column)
                            {
                                reader.GetSchemaTable().DefaultView.RowFilter = string.Format("ColumnName= '{0}'", "TotalRows");
                                has_total_row_column = (reader.GetSchemaTable().DefaultView.Count > 0);
                                if (has_total_row_column && reader["TotalRows"] != null)
                                {
                                    number_of_records = int.Parse(reader["TotalRows"].ToString());
                                    has_total_row_column = false;
                                }
                            }

                            var o = Activator.CreateInstance<T>();
                            querygen.SetObjectFromDataRow(o, reader);
                            if (o != null)
                            {
                                items.Add(o);
                            }
                        }
                    }

                }
                else
                {
                    //or just execute query, and read the identity value if have any
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            reader.GetSchemaTable().DefaultView.RowFilter = string.Format("ColumnName= '{0}'", "id");
                            if (reader.GetSchemaTable().DefaultView.Count > 0)
                            {
                                int id = int.Parse(reader["id"].ToString());
                                (exec_object as BaseObject).SetObjectId(id);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (db_transaction != null)
                {
                    db_transaction.Rollback();
                    db_transaction = null;
                }
                throw ex;
            }
            finally
            {
                if (db_transaction == null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            numRecords = number_of_records;
            return items;
        }

        public SqlConnection BeginTransaction()
        {
            if (db_transaction == null)
            {
                SqlConnection conn = GetConnection();
                conn.Open();
                db_transaction = conn.BeginTransaction();
                return conn;
            }

            return null;
        }
        public void CommitTransaction()
        {
            if (db_transaction != null)
            {
                db_transaction.Commit();
                db_transaction.Dispose();
                db_transaction = null;
            }
        }
        public void RollbackTransaction()
        {
            if (db_transaction != null)
            {
                db_transaction.Rollback();
                db_transaction.Dispose();
                db_transaction = null;
            }
        }

        public string GetFirstPrimaryKey()
        {
            return querymap.GetTableKeys(typeof(T)).FirstOrDefault();
        }

        #region private functions
        public SqlConnection GetConnection()
        {
            string connection_string = ConfigurationManager.ConnectionStrings["datacontext_cs"].ConnectionString;
            if (db_transaction != null)
                return db_transaction.Connection;
            else
                return new SqlConnection(connection_string);
        }
        #endregion


        public override Validator validator
        {
            get
            {
                return _validator;
            }
            set
            {
                _validator = value;
            }
        }
    }




}